package com.jh.mms.dao.impl;

import java.io.Serializable;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import oracle.jdbc.driver.OracleTypes;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.jh.mms.dao.ISqlDao;

@SuppressWarnings("unchecked")
public class SqlDaoImpl<T, PK extends Serializable> extends HibernateDaoSupport
		implements ISqlDao<T, PK> {

	private Session session = null;

	public Session openSession() {
		// TODO Auto-generated method stub
		if (null == session)
			session = this.getSession(true);
		return session;
	}

	public void closeSession() {
		// TODO Auto-generated method stub
		if (null != session) {
			session.close();
		}
	}

	public boolean executeSQL(String sql, List<T> params) {
		boolean result = false;
		if (null != sql && !"".equals(sql.trim())) {
			Transaction tx = null;
			try {
				session = this.getSession();
				Query query = session.createSQLQuery(sql);
				if (null != params) {
					for (int i = 0; i < params.size(); i++) {
						query.setParameter(i, params.get(i));
					}
				}
				result = query.executeUpdate() > 0;
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		return result;
	}

	private String formatSqlStr(String sql, List params) {
		if (null == params || params.size() == 0)
			return sql;
		for (int i = 0; i < params.size(); i++)
			sql = sql.replaceFirst("\\?", String.valueOf(params.get(i)));
		return sql;
	}

	public List<String> executeBatch(List<String> sql, List<T> params) {
		// TODO Auto-generated method stub
		List result = new ArrayList();
		if (null != sql && 0 < sql.size()) {
			for (int i = 0; i < sql.size(); i++) {
				String _sql = sql.get(i);
				List _par = (List) params.get(i);
				if (!this.executeSQL(_sql, _par))
					result.add(this.formatSqlStr(_sql, _par));
			}
		}
		return result;
	}

	public List<T> find(String sql) {
		// TODO Auto-generated method stub
		List result = null;
		if (null != sql && !"".equals(sql.trim())) {
			try {
				session = this.getSession();
				Query query = session.createSQLQuery(sql);
				result = query.list();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		return result;
	}

	public List<T> find(String sql, int pageRows, int pageIndex) {
		// TODO Auto-generated method stub
		List result = null;
		if (null != sql && !"".equals(sql.trim())) {
			try {
				session = this.getSession();
				Query query = session.createSQLQuery(sql);
				if (pageRows > 0 && pageIndex > 0) {
					query.setMaxResults(pageRows);
					query.setFirstResult(pageRows * (pageIndex - 1));
				}
				result = query.list();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		return result;
	}

	public List<T> find(String sql, List<T> params) {
		// TODO Auto-generated method stub
		List result = null;
		if (null != sql && !"".equals(sql.trim())) {
			try {
				session = this.getSession();
				Query query = session.createSQLQuery(sql);
				if (null != params) {
					for (int i = 0; i < params.size(); i++) {
						query.setParameter(i, params.get(i));
					}
				}
				result = query.list();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		return result;
	}

	public List<T> find(String sql, List<T> params, int pageRows, int pageIndex) {
		// TODO Auto-generated method stub
		List result = null;
		if (null != sql && !"".equals(sql.trim())) {
			try {
				session = this.getSession();
				Query query = session.createSQLQuery(sql);
				if (null != params) {
					for (int i = 0; i < params.size(); i++) {
						query.setParameter(i, params.get(i));
					}
				}
				if (pageRows > 0 && pageIndex > 0) {
					query.setMaxResults(pageRows);
					query.setFirstResult(pageRows * (pageIndex - 1));
				}
				result = query.list();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		return result;
	}

	public int listCount(String sql) {
		String start = "SELECT COUNT(0) ";
		sql = sql.toUpperCase();
		sql = start + sql.substring(sql.indexOf("FROM"), sql.length());
		List list = this.find(sql);
		return (null != list) ? Integer.parseInt(list.get(0).toString()) : -1;
	}

	public int listCount(String sql, List<T> params) {
		String start = "SELECT COUNT(0) ";
		sql = sql.toUpperCase();
		sql = start + sql.substring(sql.indexOf("FROM"), sql.length());
		List list = this.find(sql, params);
		return (null != list) ? Integer.parseInt(list.get(0).toString()) : -1;
	}

	public int execFunc(String fname, List<T> params) {
		int result = -100;
		try {
			if (null != fname && !"".equals(fname.trim())) {
				StringBuffer sql = new StringBuffer();
				sql.append("{ ? = call ").append(fname).append("(");
				for (int i = 0; i < params.size(); i++) {
					sql.append("?");
					if (i < params.size() - 1)
						sql.append(",");
				}
				sql.append(") }");

				session = this.getSession();
				CallableStatement call = session.connection().prepareCall(
						sql.toString());
				call.registerOutParameter(1, Types.INTEGER);
				for (int i = 0, j = 2; i < params.size(); i++, j++)
					call.setObject(j, params.get(i));
				if (!call.execute())
					result = call.getInt(1);
				call.close();
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return result;
	}

	public List<T> findByFunc(String fname, List<T> params) {
		// TODO Auto-generated method stub
		List result = null;
		try {
			if (null != fname && !"".equals(fname.trim())) {
				StringBuffer sql = new StringBuffer();
				sql.append("{ ? = call ").append(fname).append("(");
				for (int i = 0; i < params.size(); i++) {
					sql.append("?");
					if (i < params.size() - 1)
						sql.append(",");
				}
				sql.append(") }");

				session = this.getSession();
				CallableStatement call = session.connection().prepareCall(
						sql.toString());
				call.registerOutParameter(1, OracleTypes.CURSOR);
				for (int i = 0, j = 2; i < params.size(); i++, j++)
					call.setObject(j, params.get(i));

				if (!call.execute()) {
					result = new ArrayList();
					ResultSet rs = (ResultSet) call.getObject(1);
					ResultSetMetaData md = rs.getMetaData();
					int columnCount = md.getColumnCount();

					while (rs.next()) {
						Object[] row = new Object[columnCount];
						for (int i = 1; i <= columnCount; i++) {
							row[i - 1] = rs.getObject(i);
						}
						result.add(row);
					}
					rs.close();
				}
				call.close();
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return result;
	}

	public int getSequence(String seqName) {
		int seq = -1;
		if (seqName != null) {
			seqName = seqName.toUpperCase();
			String sql = "SELECT " + seqName + ".NEXTVAL FROM DUAL";
			List list = this.find(sql);
			if(list.size()>0){
				seq=Integer.parseInt(list.get(0).toString());
			}
		}
		return seq;
	}
}
